package com.msg.xml;

import java.util.ArrayList;
import java.util.List;

/*
 * This class provide some basic functions for manipulating or accessing the price code table
 * 1. Getting ticket Id given with a ticket level name
 * 2. Creating the request for 4 primary keys in price code table
 * 
 */
public class TableUtility {
	
	
	/**
	 * a internal utility function used to get the data from list
	 * if the list is empty, return null, else return the first string
	 * @param list : a list of strings
	 * @return : null if list is empty, first string if list is non-empty
	 */
	private static String getListData(List<String> list) {
		if(list.size() > 0)
			return list.get(0);
		else
			return null;
	}
	
	/**
	 * get the ticket type string from the string of eventCode
	 * @param eventCode : event code
	 * @return : string of ticket type
	 */
	public static String getTicketType(String eventCode) {
		if(eventCode.startsWith("EPL"))
			return "EPL";
		else if(eventCode.endsWith("(TOTAL)"))
			return eventCode.substring(0, eventCode.indexOf(" (TOTAL)")-1);
		else
			return eventCode;
	}
	
	
	/**
	 * Getting ticket id from price code table given a specific ticket level name
	 * @param ticketLv : the ticket level name
	 * @return : ticket level ID
	 */
	public static String getTicketLvID(String ticketLv) {
		DBHandler DBhandler = DBHandler.getInstance();
		DBRequest request = new DBRequest();
		request.resultCol.add("Ticket_Level_Id");
		request.entries.add("Ticket_Level_Name");
		request.data.add(ticketLv);
		List<String> result = DBhandler.getData("Ticket_Level", request);
		return getListData(result);
	}
	
	/**
	 * Get the event id from event table by using the event code
	 * @param eventCode : string of event code
	 * @return : the event id
	 */
	public static String getEventId(String eventCode) {
		DBHandler DBhandler = DBHandler.getInstance();
		DBRequest request = new DBRequest();
		request.resultCol.add("Event_Id");
		request.entries.add("Event_Code");
		request.data.add(eventCode);
		List<String> result = DBhandler.getData("event", request);
		return getListData(result);
	}
	
	/**
	 * Get the ticket type id from ticket type table by using the string of ticket type
	 * @param ticketType : string of ticket type
	 * @return : ticket type id
	 */
	public static String getTicketTypeId(String ticketType) {
		DBHandler DBhandler = DBHandler.getInstance();
		DBRequest request = new DBRequest();
		request.resultCol.add("Ticket_Type_Id");
		request.entries.add("Ticket_Type_Code");
		request.data.add(ticketType);
		List<String> result = DBhandler.getData("ticket_type", request);
		return getListData(result);
	}
	
	/**
	 * Get the price level id from price_level_index table by using the price level code
	 * @param priceLv_Code : the string of price level code
	 * @return : the price level id
	 */
	public static String getPriceLvId(String priceLv_Code) {
		DBHandler DBhandler = DBHandler.getInstance();
		DBRequest request = new DBRequest();
		request.resultCol.add("Price_Level_Id");
		request.entries.add("Price_Level_Code");
		request.data.add(priceLv_Code);
		List<String> result = DBhandler.getData("price_level_index", request);
		return getListData(result);
	}
	
	/**
	 * Get the ticket group id from ticket group table by using the ticket type id and price level id
	 * @param ticketTypeId : ticket type id
	 * @param priceLvId : price level id
	 * @return : ticket group id
	 */
	public static String getTicketGroupId(String ticketTypeId, String priceLvId) {
		DBHandler DBhandler = DBHandler.getInstance();
		DBRequest request = new DBRequest();
		request.resultCol.add("Ticket_Group_Id");
		request.entries.add("Ticket_Type_Id");
		request.entries.add("Price_Level_Id");
		request.data.add(ticketTypeId);
		request.data.add(priceLvId);
		List<String> result = DBhandler.getData("ticket_group", request);
		return getListData(result);
	}
	
	
	/**
	 * create a request with 2 primary keys shared in {@code Price_Code} and
	 * {@code Remaining_Sale_Prediction} tables
	 * @param ticketLvId : ticket level's Id
	 * @param resultColumn : the return result of query
	 * @param eventId : the id for a event code
	 * @return a DBRequest object with 2 primary keys ({@code Event_Id}, {@code Ticket_Level_Id})
	 */
	public static DBRequest bindPrimaryKeys(String ticketLvId, String resultColumn, String eventId) {
		DBRequest request = new DBRequest();
		request.resultCol.add(resultColumn);
		request.entries.add("Event_Id");
		request.entries.add("Ticket_Level_Id");
		request.data.add(eventId);
		request.data.add(ticketLvId);
		return request;
	}
	
	/**
	 * bind the ticket group key into the request generated by {@code bindPrimaryKeys}
	 * @param ticketLvId : ticket level id
	 * @param groupId : the ticket's group id
	 * @param resultColumn : the return result of query
	 * @param eventId : the id for a event code
	 * @return a DBRequest object with 3 primary keys ({@code Event_Id}, {@code Ticket_Level_Id}, 
	 * {@code Ticket_Group_Id})
	 */
	public static DBRequest bindGroupKeys(String ticketLvId, String groupId, String resultColumn, String eventId) {
		DBRequest request = bindPrimaryKeys(ticketLvId, resultColumn, eventId);
		request.entries.add("Ticket_Group_Id");
		request.data.add(groupId);
		return request;
	}
	
	
	/**
	 * bind the ticket group prediction key into the request generated by {@code bindPrimaryKeys}
	 * @param ticketLvId : ticket level id
	 * @param predictionId : the ticket's group prediction id
	 * @param resultColumn : the return result of query
	 * @param eventId : the id for a event code
	 * @return a DBRequest object with 3 primary keys ({@code Event_Id}, {@code Ticket_Level_Id}, 
	 * {@code Ticket_Group_Prediction_Id})
	 */
	public static DBRequest bindPredictionKeys(String ticketLvId, String predictionId, String resultColumn, String eventId) {
		DBRequest request = bindPrimaryKeys(ticketLvId, resultColumn, eventId);
		request.entries.add("Ticket_Group_Prediction_Id");
		request.data.add(predictionId);
		return request;
	}
	
	/**
	 * get the group id from ticket_group table
	 * @return
	 */
	public static String getGroupID(String ticketTypeId, String priceLvId) {
		DBRequest request = new DBRequest();
		request.resultCol.add("Ticket_Group_Id");
		
		request.entries.add("Ticket_Type_Id");
		request.entries.add("Price_Level_Id");
		
		request.data.add(ticketTypeId);
		request.data.add(priceLvId);
		
		DBHandler DBhandler = DBHandler.getInstance();
		return DBhandler.getData("ticket_group", request).get(0);
	}
	
	/**
	 * check if the sum of tickets with given prediction id is bigger than 0	
	 * @param predictionId, a ticket_group_prediction_id
	 * @param eventId, the event id of current event
	 * @return, whether the sum of number of seats bigger than 0
	 */
	public static boolean checkSalePrediction(String predictionId, String eventId) {
		String query = "select sum(Number_Of_Seats) from sale_prediction where Event_Id=" + eventId + " and Ticket_Group_Prediction_Id<>7";
		DBHandler DBhandler = DBHandler.getInstance();
		List<String> result = DBhandler.runQuery(query);
		if(result.get(0) == null)
			return false;
		int ticketSum = Integer.parseInt(result.get(0));
		if(ticketSum == 0)
			return false;
		else
			return true;
	}
}
